from datetime import datetime, timezone
from typing import Optional, Dict, Any, List

from pydantic import BaseModel, Field, field_serializer
from sqlalchemy import Column, BigInteger, String, Text, Integer, text, bindparam
from sqlalchemy.orm import declarative_base

from tools.db_model_tool import transactional, db_session

Base = declarative_base()


# ORM 数据表模型
class CodeScript(Base):
    __tablename__ = "tb_code_script"

    id = Column(BigInteger, primary_key=True, index=True, autoincrement=True, comment="主键ID")
    code_key = Column(String(100), unique=True, nullable=False, comment="脚本唯一标识")
    name = Column(String(200), nullable=False, comment="脚本名称")
    code = Column(Text, nullable=False, comment="Python代码内容")
    is_del = Column(Integer, default=1, nullable=False, comment="是否删除(0=删除, 1=未删除)")


# 分页查询 DTO
class CodeScriptQueryDTO(BaseModel):
    page: int = Field(1, description="当前页码，从 1 开始")
    page_size: int = Field(10, description="每页条数")
    sort_by: str = Field("id", description="排序字段")
    sort_order: str = Field("ASC", description="排序顺序，ASC 或 DESC")
    filters: Optional[Dict[str, Any]] = Field(None, description="过滤条件，字段->值 或 字段->值列表 映射")


# 创建/更新 DTO
class CodeScriptCreateDTO(BaseModel):
    code_key: Optional[str] = Field(None, description="脚本唯一标识")
    name: Optional[str] = Field(None, description="脚本名称")
    code: Optional[str] = Field(None, description="Python代码内容")


# 展示/返回 VO
class CodeScriptVO(BaseModel):
    id: Optional[int] = Field(None, description="主键ID")
    code_key: Optional[str] = Field(None, description="脚本唯一标识")
    name: Optional[str] = Field(None, description="脚本名称")
    code: Optional[str] = Field(None, description="Python代码内容")
    is_del: Optional[int] = Field(None, description="是否删除(0=删除, 1=未删除)")
    create_time: Optional[str] = Field(None, description="创建时间")
    update_time: Optional[str] = Field(None, description="更新时间")

    @field_serializer("created_at", "updated_at", check_fields=False)
    def serialize_datetime(self, dt: datetime) -> str:
        return dt.strftime("%Y-%m-%d %H:%M:%S")

    # 支持从 ORM 模型转换而来
    class Config:
        from_attributes = True


# 流式请求数据
class CodePayload(BaseModel):
    code: str


@transactional
def create_code_script(script_in: CodeScriptCreateDTO, db) -> CodeScriptVO:
    # 获取当前时间，用于创建和更新时间
    now = datetime.now(timezone.utc)

    # 定义 SQL 插入语句，并使用绑定参数
    sql = text("""
        INSERT INTO tb_code_script (code_key, name, code, is_del, created_at, updated_at)
        VALUES (:code_key, :name, :code, 1, :created_at, :updated_at)
    """).bindparams(
        bindparam("code_key"),
        bindparam("name"),
        bindparam("code"),
        bindparam("is_del", value=1),  # 显式设置默认值为 1
        bindparam("created_at"),
        bindparam("updated_at")
    )

    # 从 DTO 中获取参数，并添加创建和更新时间
    params = script_in.dict(exclude_unset=True)
    params["created_at"] = now
    params["updated_at"] = now

    # 执行插入语句
    result = db.execute(sql, params)

    # 获取插入后的 ID（如果需要）
    new_id = result.lastrowid  # 获取最后插入的 ID

    # 如果需要可以返回插入的数据
    row = db.execute(
        text("SELECT * FROM tb_code_script WHERE id = :id"),
        {"id": new_id}
    ).fetchone()

    return CodeScriptVO(**row._mapping)


@db_session
def get_code_script_list(query: CodeScriptQueryDTO, db) -> List[CodeScriptVO]:
    where_clauses_code = ["is_del = 1"]
    params: Dict[str, Any] = {}

    # 处理过滤条件，支持列表筛选
    if query.filters:
        for key, val in query.filters.items():
            if isinstance(val, (list, tuple)) and val:
                # 这里使用 IN 而不是 ANY，以兼容更多的数据库
                where_clauses_code.append(f"{key} IN (:{key})")
                params[key] = list(val)
            else:
                where_clauses_code.append(f"{key} = :{key}")
                params[key] = val

    # 分页参数
    offset = (query.page - 1) * query.page_size
    params.update({"limit": query.page_size, "offset": offset})

    # 安全性：确保排序字段是合法的，避免 SQL 注入
    allowed_sort_fields = ['id', 'code_key', 'name', 'create_time', 'update_time']
    if query.sort_by not in allowed_sort_fields:
        raise ValueError(f"Invalid sort_by value: {query.sort_by}")

    allowed_sort_orders = ['ASC', 'DESC']
    if query.sort_order not in allowed_sort_orders:
        raise ValueError(f"Invalid sort_order value: {query.sort_order}")

    # 构造 SQL 查询
    sql = text(f"""
        SELECT * FROM tb_code_script
        WHERE {" AND ".join(where_clauses_code)}
        ORDER BY {query.sort_by} {query.sort_order}
        LIMIT :limit OFFSET :offset
    """)

    # 执行查询
    rows = db.execute(sql, params).fetchall()

    # 使用 _mapping 来转换查询结果为字典并返回 VO 列表
    return [CodeScriptVO(**row._mapping) for row in rows]


@transactional
def update_code_script(script_id: int, update_data: CodeScriptCreateDTO, db):
    fields = update_data.dict(exclude_unset=True)
    if not fields:
        return False

    set_clause = ', '.join([f"{key} = :{key}" for key in fields.keys()])
    sql = f"""
        UPDATE tb_code_script
        SET {set_clause}
        WHERE id = :id AND is_del = 1
    """
    fields["id"] = script_id
    result = db.execute(text(sql), fields)
    db.commit()
    return '更新了'
